	if exists (select 1 from sysobjects where name = 'set_updsetcolor' and type = 'P')
begin
   drop procedure set_updsetcolor
   print 'Procedure: set_updsetcolor deleted ...'
end
go
create procedure set_updsetcolor(
  @planid         int = 1,
  @prsid          int = 1,
  @abteilungid    int = 1,
  @datum          char(8) = '20080702',
  @argb           int = 0
)
as
begin
  set nocount on

   declare @dte datetime
   select @dte = convert(datetime,@datum,112)

   if @argb = 0
   begin --delete dienst
     delete from ColorPlan
     where PlanID = @planid
       and PrsID = @prsid
       and Datum = @dte
       and AbteilungID = @abteilungid
   end
   else --add dienst
   begin
     if exists (select * from ColorPlan dp
                 where dp.PlanID = @planid
                   and dp.PrsID = @prsid
                   and dp.Datum = @dte
                   and dp.AbteilungID = @abteilungid)
       begin
         update ColorPlan
            set ARGB = @argb,
                Colorname = ''
          where PlanID = @planid
            and PrsID = @prsid
            and Datum = @dte
            and AbteilungID = @abteilungid
       end
       else --new record
       begin
         insert ColorPlan
                (PlanID,
                 PrsID,
                 Datum,
                 AbteilungID,
                 Colorname,
                 ARGB)
         values (@planid,
                 @prsid,
                 @dte,
                 @abteilungid,
                 '',
                 @argb)
       end
     end
     
end
go
print 'Procedure: set_updsetcolor done ...'
go

grant exec on set_updsetcolor to prsadmins with grant option
go
grant exec on set_updsetcolor to prsusers
go

